﻿using System;
using System.Text;
using ApexSql.Code.SqlServer;

namespace cgu
{
	public static class ActiveTableUiUtil
	{
		public enum enmControlViewType
		{
			enmGridView,
			enmDetailsView
		}

		//---------------------------------------------
		#region private member vars and public accessors and methods
		//---------------------------------------------

		private static Table _ActiveTable;
		public static Table ActiveTable
		{
			get { return _ActiveTable; }
		}

		private static string _ClassName;
		public static string ClassName
		{
			get { return _ClassName; }
		}

		private static string _TableName;
		public static string TableName
		{
			get { return _TableName; }
		}

		private static string _UiGridKeyNameList;
		public static string UiGridKeyNameList
		{
			get { return _UiGridKeyNameList; }
		}

		private static string _UiGridColumnBindings;
		public static string UiGridColumnBindings
		{
			get { return _UiGridColumnBindings; }
		}

		private static string _UiDetailsViewDataSourceDefinition;
		public static string UiDetailsViewDataSourceDefinition
		{
			get { return _UiDetailsViewDataSourceDefinition; }
		}

		private static string _TopLevelNameSpaceForGeneratedFiles;
		public static string TopLevelNameSpaceForGeneratedFilesForGeneratedFiles
		{
			get { return _TopLevelNameSpaceForGeneratedFiles; }
		}

		private static string _LibraryNameSpace;
		public static string LibraryNameSpace
		{
			get { return _LibraryNameSpace; }
		}

		private static bool _ExcludeTempFields;
		public static bool ExcludeTempFields
		{
			get { return _ExcludeTempFields; }
		}

		private static bool _DateStampFieldsAreReadOnly;
		public static bool DateStampFieldsAreReadOnly
		{
			get { return _DateStampFieldsAreReadOnly; }
		}

		private static StringBuilder _UiTableHyperLinksIn;
		public static string UiTableHyperLinksIn
		{
			get { return _UiTableHyperLinksIn.ToString(); }
		}

		private static StringBuilder _UiTableHyperLinksOut;
		public static string UiTableHyperLinksOut
		{
			get { return _UiTableHyperLinksOut.ToString(); }
		}

		private static StringBuilder _UiHyperLinkNavUpdatesIn;
		public static string UiHyperLinkNavUpdatesIn
		{
			get { return _UiHyperLinkNavUpdatesIn.ToString(); }
		}

		private static StringBuilder _UiHyperLinkNavUpdatesOut;
		public static string UiHyperLinkNavUpdatesOut
		{
			get { return _UiHyperLinkNavUpdatesOut.ToString(); }
		}

		private static string _KeyDataSourceDropDownItems;
		public static string KeyDataSourceDropDownItems
		{
			get { return _KeyDataSourceDropDownItems; }
		}

		private static int _DotNetFrameworkTarget;
		public static int DotNetFrameworkTarget
		{
			get { return _DotNetFrameworkTarget; }
		}

		/// <summary>
		/// 'Initializes' the current table.  
		///	The idea is to call helper functions just once per table and to limit the
		/// number of visible objects available to the caller (hide all of those helper functions).
		/// </summary>
		public static void TableUiInit(Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, string sTopLevelNameSpaceForGeneratedFiles, string sLibraryNameSpace, int iDotNetFrameworkTarget)
		{
			_ActiveTable = objTable;
			_ExcludeTempFields = bExcludeTempFields;
			_DateStampFieldsAreReadOnly = bDateStampFieldsAreReadOnly;
			_DotNetFrameworkTarget = iDotNetFrameworkTarget; // 20 = 2.0, 35 = 3.5, etc

			_TopLevelNameSpaceForGeneratedFiles = sTopLevelNameSpaceForGeneratedFiles;
			_LibraryNameSpace = sLibraryNameSpace;
			_TableName = objTable.Name;
			_ClassName = cgu.CommonUtil.GetTableClassName(objTable);

			_UiGridKeyNameList = GetUiGridKeyNameList(_ActiveTable);
			_UiGridColumnBindings = GetUiGridColumnBindings(_ActiveTable, bExcludeTempFields, 6);
			_UiDetailsViewDataSourceDefinition = GetDetailsViewDataSourceDefinition(_ActiveTable, 1);
			_KeyDataSourceDropDownItems = GetDataSourceDropDownItems(_ActiveTable, 2);
			AssignHyperLinksVars(_ActiveTable, 0); // populates _UiHyperLinkNavUpdates and UiTableHyperLinks

			return;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="sFunctionName">Name of function in data access class to call</param>
		/// <param name="iDataSourceSequenceNumber">Counter for assigning sequential and unique names to the datasources</param>
		/// <param name="iIndentLevel">Base indent level (tabs)</param>
		/// <param name="kForeignKey">Foreign key. Null if not using foreign keys to build this datasource (parameters)</param>
		/// <param name="PrimaryKeyColumns">Primary key. Null if not using primary key to build this datasource (parameters)</param>
		/// <returns></returns>
		public static string UiGridViewDataSourceDefinition(string sFunctionName, int iDataSourceSequenceNumber, int iIndentLevel, ColumnCollection PrimaryKeyColumns, Key kForeignKey)
		{
			return GetGridViewDataSourceDefinition(sFunctionName, iDataSourceSequenceNumber, PrimaryKeyColumns, kForeignKey, iIndentLevel);
		}

		public static string UiDropDownDataSourceDefinition(Key kForeignKey, int iIndentLevel)
		{
			return GetDropDownDataSourceDefinition(kForeignKey, iIndentLevel);
		}

		public static string UiDetailsColumnBindings(bool bCreateDetailsViewFkDropDowns)
		{
			return GetUiDetailsViewColumnBindings(_ActiveTable, _ExcludeTempFields, _DateStampFieldsAreReadOnly, bCreateDetailsViewFkDropDowns, 5);
		}

		// ************************************************************************************
		#endregion private member vars and public accessors and methods
		// ************************************************************************************

		// ************************************************************************************
		#region table ui-related functions
		// ************************************************************************************

		/// <summary>
		/// Builds links and NavigateUrl updates to tables with fk reference this table
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="iIndentLevel"></param>
		private static void AssignHyperLinksVars(Table objTable, int iIndentLevel)
		{
			int i = 0;
			_UiTableHyperLinksIn = new StringBuilder();
			_UiHyperLinkNavUpdatesIn = new StringBuilder();

			//build link detail for in-bound referencing tables
			foreach (Table tbl in objTable.Parent.Tables)
			{
				foreach (Key fk in tbl.ForeignKeys)
				{
					if (fk.ReferencedTable.Name == _ActiveTable.Name)
					{
						i++;
						//link tag
						_UiTableHyperLinksIn.Append('\t', iIndentLevel);
						_UiTableHyperLinksIn.AppendFormat("<asp:HyperLink ID=\"hl_{0}\" runat=\"server\" NavigateUrl=\"Ui_{1}.aspx\" ForeColor=\"Navy\" Target=\"_blank\" Text=\"{1}\"></asp:HyperLink>\r\n",
																		i.ToString(),
																		cgu.CommonUtil.GetTableClassName(tbl));

						//update NavigateUrl IN  todo: only 1 key supported
						_UiHyperLinkNavUpdatesIn.Append('\t', iIndentLevel + 2);
						_UiHyperLinkNavUpdatesIn.AppendFormat("hl_{0}.NavigateUrl = \"Ui_{1}.aspx?fk={2}&value=\" + gvList.SelectedValue.ToString();\r\n",
																		i.ToString(),
																		cgu.CommonUtil.GetTableClassName(tbl),
																		fk.ReferencedKey.KeyColumns[0].Name);

						//update text IN with querystring value (to visually indicate the change)
						_UiHyperLinkNavUpdatesIn.Append('\t', iIndentLevel + 2);
						_UiHyperLinkNavUpdatesIn.AppendFormat("hl_{0}.Text = \"{1}?&nbsp;\" + gvList.SelectedValue.ToString();\r\n",
																		i.ToString(),
																		cgu.CommonUtil.GetTableClassName(tbl));
					}
				}
			}

			//build link detail for out-bound referenced tables
			_UiTableHyperLinksOut = new StringBuilder();
			_UiHyperLinkNavUpdatesOut = new StringBuilder();
			_UiHyperLinkNavUpdatesOut.Append('\t', iIndentLevel + 2);
			_UiHyperLinkNavUpdatesOut.Append("string sOutValue;\r\n");

			foreach (Key fk in objTable.ForeignKeys)
			{
				i++;
				//link tag
				_UiTableHyperLinksOut.Append('\t', iIndentLevel + 2);
				_UiTableHyperLinksOut.AppendFormat(
																	"<asp:HyperLink ID=\"hl_{0}\" runat=\"server\" NavigateUrl=\"Ui_{1}.aspx\" ForeColor=\"Navy\" Target=\"_blank\" Text=\"{1}\"></asp:HyperLink>\r\n",
																	i.ToString(),
																	cgu.CommonUtil.GetTableClassName(fk.ReferencedTable));

				//update NavigateUrl OUT
				_UiHyperLinkNavUpdatesOut.Append('\t', iIndentLevel + 2);
				_UiHyperLinkNavUpdatesOut.AppendFormat("sOutValue = gvList.SelectedRow.Cells[getGridColumnIndex(\"{0}\")].Text;\r\n",
																fk.KeyColumns[0].Name);
				_UiHyperLinkNavUpdatesOut.Append('\t', iIndentLevel + 2);
				_UiHyperLinkNavUpdatesOut.AppendFormat("hl_{0}.NavigateUrl = \"Ui_{1}.aspx?pk={2}&value=\" + sOutValue;\r\n",
																i.ToString(),
																cgu.CommonUtil.GetTableClassName(fk.ReferencedTable),
																cgu.CommonUtil.GetColumnPropertyName(fk.ReferencedTable.PrimaryKeyColumns[0].Name));


				//update text OUT with querystring value (to visually indicate the change)
				_UiHyperLinkNavUpdatesOut.Append('\t', iIndentLevel + 2);
				_UiHyperLinkNavUpdatesOut.AppendFormat("hl_{0}.Text = \"{1}?&nbsp;\" + sOutValue;", //no newline on last append
																i.ToString(),
																cgu.CommonUtil.GetTableClassName(fk.ReferencedTable));
			}
		}

		/// <summary>
		/// Creates foreign and primary key list items for triggering a change in the grid's datasource.
		/// </summary>
		/// <param name="objTable"></param>
		private static string GetDataSourceDropDownItems(Table objTable, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder();
			int j = 2; //2 items (SelectALl & Select DataTable) are already in the list
			string sColumnKeyNames = "";
			bool bPkFound = false;

			//--------------------------------------------------------------------------
			//primary keys 
			//--------------------------------------------------------------------------
			//fetch primary key columns to add a "primary key lookup" option to the dropdown
			foreach (Column objCol in ActiveTable.PrimaryKeyColumns)
			{
				//create delimited string of keycolumns values 
				j++;
				bPkFound = true;
				
				//separate key column names with "~". Currently, only one-column keys are supported.
				sColumnKeyNames += objCol.Name + "~";
			}

			//fetch first string-based field in table for text portion of a foreign key lookup  (todo: better way to do this?)
			string sTextColName = "";
			foreach (Column col in objTable.Columns)
			{
				if (cgu.CommonUtil.IsColumnStringType(col) && !col.IsInPrimaryKey) //no need to lookup a text value if the text value is the primary key
				{
					sTextColName += col.Name;
					break;
				}
			}

			//add Primary Key lookup option to list. todo: only 1 pk value supported
			if (bPkFound)
			{
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("<asp:ListItem Value=\"" + sColumnKeyNames + "^" + sTextColName + "\">By " +
														 sColumnKeyNames.Substring(0, sColumnKeyNames.Length-1) + "</asp:ListItem>\r\n");
			}

			//--------------------------------------------------------------------------
			//foreign keys (todo: only one key column is supported)
			//--------------------------------------------------------------------------
			foreach (Key fk in ActiveTable.ForeignKeys)
			{
				//create delimited string of keycolumns values 
				j++;
				sColumnKeyNames = "";

				//delimit key column names with "~". Currently, only one-column keys are supported.
				foreach (Column col in fk.KeyColumns)
				{
					sColumnKeyNames += col.Name + "~";
				}

				//fetch first string-based field (perhaps there's a better way to do this?) in fk table and save it to the string
				sTextColName = "";
				foreach (Column col in fk.ReferencedTable.Columns)
				{
					if (cgu.CommonUtil.IsColumnStringType(col))
					{
						sTextColName += col.Name + "~";
						break;
					}
				}

				//add list item to list
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("<asp:ListItem Value=\"" + sColumnKeyNames + "^" + sTextColName + "\">By " + sColumnKeyNames.Substring(0, sColumnKeyNames.Length - 1) + "</asp:ListItem>\r\n");
			}

			//return list of listitems
			return sBuilder.ToString();
		}

		/// <summary>
		/// Get key names for the asp.net GridView
		/// </summary>
		/// <param name="objTable"></param>
		/// <returns></returns>
		private static string GetUiGridKeyNameList(Table objTable)
		{
			StringBuilder sBuilder = new StringBuilder();

			foreach (Column objColumn in objTable.Columns)
			{
				if (objColumn.IsInPrimaryKey)
				{
					//add key name (in property-name format)
					sBuilder.Append(cgu.CommonUtil.GetColumnPropertyName(objColumn.Name) + ",");
				}
			}

			//remove trailing comma
			if (sBuilder.Length > 0)
			{
				sBuilder = sBuilder.Remove(sBuilder.Length - 1, 1);
			}

			//return
			return sBuilder.ToString();
		}

		/// <summary>
		/// Build asp.net gridview column databindings
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exlclude columns ending with "Temp"</param>
		/// <param name="iIndentLevel">Indent Level (number of tabs to indent)</param>
		/// <returns></returns>
		private static string GetUiGridColumnBindings(Table objTable, bool bExcludeTempFields, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder();
			int i = 0;

			//fetch primary key params first
			foreach (Column objColumn in objTable.Columns)
			{
				if (objColumn.IsInPrimaryKey)
				{
					i++;

					if (i > 1)
					{
						sBuilder.Append('\t', iIndentLevel);
					}
					sBuilder.AppendFormat("{0}\r\n", GetUiColumnBinding(enmControlViewType.enmGridView, objTable, objColumn, iIndentLevel, true, true, false));
				}
			}

			//followed by non-primaries
			foreach (Column objColumn in objTable.Columns)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					if (!objColumn.IsInPrimaryKey)
					{
						i++;
						sBuilder.Append('\t', iIndentLevel);
						sBuilder.AppendFormat("{0}\r\n",  GetUiColumnBinding(enmControlViewType.enmGridView, objTable, objColumn, iIndentLevel, true, true, false));
					}
				}
			}

			//remove trailing newline and return
			return sBuilder.Remove(sBuilder.Length - 1, 1).ToString();
		}

		/// <summary>
		/// Build asp.net detailsview column databindings
		/// </summary>
		/// <param name="objTable"></param>
		/// <param name="bExcludeTempFields">Optionally exlclude columns ending with "Temp"</param>
		/// <param name="bDateStampFieldsAreReadOnly">Optionally exlclude Data Stamp fields</param>
		/// <param name="bCreateDetailsViewFkDropDowns">Add templated dropdownlists for non-pk fk columns</param>
		/// <param name="iIndentLevel">Indent Level (number of tabs to indent)</param>
		/// <returns></returns>
		private static string GetUiDetailsViewColumnBindings(Table objTable, bool bExcludeTempFields, bool bDateStampFieldsAreReadOnly, bool bCreateDetailsViewFkDropDowns, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder();
			int i = 0;

			//fetch primary key params first
			foreach (Column objColumn in objTable.Columns)
			{
				if (objColumn.IsInPrimaryKey)
				{
					i++;

					if (i > 1)
					{
						sBuilder.Append('\t', iIndentLevel);
					}
					sBuilder.AppendFormat("{0}\r\n", GetUiColumnBinding(enmControlViewType.enmDetailsView, objTable, objColumn, iIndentLevel, true, !objColumn.IsIdentity, false));
				}
			}

			//followed by non-primaries
			foreach (Column objColumn in objTable.Columns)
			{
				if (!cgu.CommonUtil.ColumnExcludeCheck(objColumn.Name, bExcludeTempFields))
				{
					if (!objColumn.IsInPrimaryKey)
					{
						i++;
						bool bIsReadOnlyField = cgu.CommonUtil.ColumnIsAutoFilled(objColumn, bDateStampFieldsAreReadOnly);
						sBuilder.Append('\t', iIndentLevel);
						sBuilder.AppendFormat("{0}\r\n", GetUiColumnBinding(enmControlViewType.enmDetailsView, objTable, objColumn, iIndentLevel, bIsReadOnlyField, !bIsReadOnlyField, bCreateDetailsViewFkDropDowns));
					}
				}
			}

			//add commandfield. Blank out NewText in favor of using a Button to trigger insert mode
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.Append("<asp:CommandField ShowDeleteButton=\"True\" ShowEditButton=\"True\" ShowInsertButton=\"True\" NewText=\"\" />");

			//return
			return sBuilder.ToString();
		}

		/// <summary>
		/// Build asp.net objectdatasource parameter list
		/// </summary>
		/// <returns></returns>
		private static string GetDetailsViewDataSourceDefinition(Table objTable, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");

			//add outer definition
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.Append("<!-- DatailsView -->\r\n");
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat(
				"<asp:ObjectDataSource ID=\"dataDetailSource\" runat=\"server\" SelectMethod=\"SelectByPk\" UpdateMethod=\"Update\" DeleteMethod=\"Delete\" InsertMethod=\"Insert\" TypeName=\"{0}\">\r\n",
															_TopLevelNameSpaceForGeneratedFiles + ".Dal." + ClassName + "_Dal");

			//---------------------------------------------------------
			//Add SELECT parameters (primary keys)
			//---------------------------------------------------------
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<SelectParameters>\r\n");
			foreach (Column objColumn in objTable.PrimaryKeyColumns)
			{
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("{0}\r\n", GetDataSourceControlParameterDefByDataKey(objColumn, "gvList", cgu.CommonUtil.GetColumnPropertyName(objColumn.Name)));
			}
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</SelectParameters>\r\n");

			//---------------------------------------------------------
			//Add UPDATE parameters 
			//---------------------------------------------------------
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<UpdateParameters>\r\n");
	
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</UpdateParameters>\r\n");

			//---------------------------------------------------------
			//Add INSERT parameters. 
			//Add the PK on the insert just so that the signature used by the ObjectDataSource on the InsertMethod is the same
			//  as the UpdateMethod (which automatically includes the PK because it is displayed). Otherwise, another constructor
			//  on the entity object would have to be built (with all the params minus the pk).
			//---------------------------------------------------------
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<InsertParameters>\r\n");

			sBuilder.Append('\t', iIndentLevel + 1);
			foreach (Column objColumn in objTable.PrimaryKeyColumns)
			{
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("{0}\r\n", GetDataSourceStandardParameterDef(objColumn));
			}
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</InsertParameters>\r\n");

			//---------------------------------------------------------
			//Add Delete parameters (primary keys)
			//---------------------------------------------------------
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<DeleteParameters>\r\n");

			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</DeleteParameters>\r\n");

			//close definition
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.Append("</asp:ObjectDataSource>");

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds a GridView objectdatasource for primary or foreign keys
		/// </summary>
		/// <returns></returns>
		private static string GetGridViewDataSourceDefinition(string sFunctionName, int iDataSourceSequenceNumber, ColumnCollection PrimaryKeyColumns, Key kForeignKey, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat("<!-- GridView #{0}: {1}-->\r\n", iDataSourceSequenceNumber, sFunctionName);

			//add outer definition
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat(
				"<asp:ObjectDataSource ID=\"dataGridSource{1}\" runat=\"server\" SelectMethod=\"{2}\" TypeName=\"{0}\">\r\n",
											_TopLevelNameSpaceForGeneratedFiles + ".Dal." + ClassName + "_Dal", iDataSourceSequenceNumber, sFunctionName);


			//add selectparams tag
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<SelectParameters>\r\n");

			//--------------------------------------------------------
			//Add foreign key SELECT parameters (if not null)
			//--------------------------------------------------------	
			if (kForeignKey != null)
			{
				int i = 0;
				//note: only one column is currently supported
				foreach (Column objColumn in kForeignKey.KeyColumns)
				{
					i++;
					sBuilder.Append('\t', iIndentLevel + 2);
					sBuilder.AppendFormat("{0}\r\n", GetDataSourceControlParameterDef(objColumn, "ddParam" + i.ToString(), "SelectedValue"));
				}
			}

			//--------------------------------------------------------
			//Add primary key SELECT parameters (if not null)
			//--------------------------------------------------------
			if (PrimaryKeyColumns != null)
			{
				int i = 0;
				//note: only one column is currently supported
				foreach (Column objColumn in PrimaryKeyColumns)
				{
					i++;
					sBuilder.Append('\t', iIndentLevel + 2);
					sBuilder.AppendFormat("{0}\r\n", GetDataSourceControlParameterDef(objColumn, "txtParam" + i.ToString(), "Text"));
				}
			}

			//close selectparams tag
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</SelectParameters>\r\n");

			//close definition
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.Append("</asp:ObjectDataSource>");

			return sBuilder.ToString();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="kForeignKey"></param>
		/// <param name="iIndentLevel"></param>
		/// <returns></returns>
		private static string GetDropDownDataSourceDefinition(Key kForeignKey, int iIndentLevel)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");

			//add datasource for each foreign key. only 1 column fk's are support

			//objectdatasource
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat("<!-- Foreign Key -- Name: {0} Column: {1} -->\r\n", kForeignKey.Name, kForeignKey.KeyColumns[0].Name);
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat(
							"<asp:ObjectDataSource ID=\"dataDropDownFk{0}\" runat=\"server\" SelectMethod=\"{1}\" TypeName=\"{2}\">\r\n",
							cgu.CommonUtil.GetColumnPropertyName(kForeignKey.KeyColumns[0].Name),
							"SelectAll",
							_TopLevelNameSpaceForGeneratedFiles + ".Dal." + cgu.CommonUtil.GetTableClassName(kForeignKey.ReferencedTable) + "_Dal");

			//params
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<SelectParameters>\r\n");

			//sBuilder.Append('\t', iIndentLevel + 2);
			//sBuilder.AppendFormat("{0}\r\n", GetDataSourceControlParameterDef(objColumn, "ddParam" + i.ToString(), "SelectedValue"));

			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</SelectParameters>\r\n");

			//close definition
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.Append("</asp:ObjectDataSource>\r\n");

			return sBuilder.ToString();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="objColumn"></param>
		/// <param name="sControlId"></param>
		/// <param name="sControlProperty"></param>
		/// <returns></returns>
		private static string GetDataSourceControlParameterDef(Column objColumn, string sControlId, string sControlProperty)
		{
			StringBuilder sBuilder = new StringBuilder();
			//single key version
			sBuilder.AppendFormat("<asp:ControlParameter ControlId=\"{0}\" Name=\"{1}\" PropertyName=\"{4}\" Type=\"{2}\" {3}/>", sControlId,
																cgu.CommonUtil.GetColumnPropertyName(objColumn), cgu.CommonUtil.GetClrType(objColumn), GetConvertToNull(objColumn), sControlProperty);

			return sBuilder.ToString();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="objColumn"></param>
		/// <param name="sControlId"></param>
		/// <param name="sDataKeyName"></param>
		/// <returns></returns>
		private static string GetDataSourceControlParameterDefByDataKey(Column objColumn, string sControlId, string sDataKeyName)
		{
			StringBuilder sBuilder = new StringBuilder();
			//single key version
			//multi-key version
			sBuilder.AppendFormat("<asp:ControlParameter ControlId=\"{0}\" Name=\"{1}\" PropertyName=\"SelectedDataKey.Values[{4}]\" Type=\"{2}\" {3}/>", sControlId,
																cgu.CommonUtil.GetColumnPropertyName(objColumn),
																cgu.CommonUtil.GetClrType(objColumn),
																GetConvertToNull(objColumn),
																sDataKeyName);

			return sBuilder.ToString();
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="objColumn"></param>
		/// <returns></returns>
		private static string GetDataSourceStandardParameterDef(Column objColumn)
		{
			StringBuilder sBuilder = new StringBuilder();
			string sType = objColumn.AdoNetMapping.CLRType;

			sBuilder.AppendFormat("<asp:Parameter Name=\"{0}\" Type=\"{1}\" {2}/>", cgu.CommonUtil.GetColumnPropertyName(objColumn), sType, GetConvertToNull(objColumn));

			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds the library function call to add the appropriate parameter call to the list.
		/// </summary>
		private static string GetConvertToNull(Column objColumn)
		{
			if (cgu.CommonUtil.IsColumnStringType(objColumn) && !objColumn.IsNullable)
			{
				string sSqlDataType = objColumn.SQLDatatype;

				switch (sSqlDataType)
				{
					case "char":
					case "varchar":
					case "nchar":
					case "nvarchar":

						return " ConvertEmptyStringToNull=\"false\" ";
				}
			}

			return ""; // will default to true
		}

		/// <summary>
		/// Builds the "<asp:BoundField" definition for a GridView or DetailsView 
		/// </summary>
		internal static string GetUiColumnBinding(enmControlViewType enmView, Table objTable, Column objColumn, int iIndentLevel, bool bReadOnly, bool bInsertVisible, bool bCreateDetailsViewFkDropDowns)
		{
			StringBuilder sBuilder = new StringBuilder();
			StringBuilder sBuilderAdd = new StringBuilder();
			//ConvertEmptyStringToNull="False"

			//-------------------------------------------------------------------------------------------------------------------------------
			//if the column is a non-primary foreign key, return a templated def with a drop-down showing the text value of the referenced fk.
			//if the dropdown can't be built, return a normal "bound" column def
			//-----------------------------------------------------------------------------------------------------------------------------
			if (!objColumn.IsInPrimaryKey && objColumn.IsInForeignKey && enmView == enmControlViewType.enmDetailsView && bCreateDetailsViewFkDropDowns)
			{
				string sDdBuilder = GetUiGridColumnFkDdTemplate(enmView, objTable, objColumn, iIndentLevel, bReadOnly, bInsertVisible);
				if (sDdBuilder.Length > 0)
				{
					return sDdBuilder;
				}
			}

			//------------------------------------------------------------------------
			//if column is bit, make it a checkbox column
			//------------------------------------------------------------------------
			if (objColumn.SQLDatatype == "bit")
			{
				sBuilder.Append('\t', iIndentLevel);
				sBuilder.AppendFormat("<asp:TemplateField ControlStyle-CssClass=\"amGridControlStyle\" HeaderText=\"{0}\">\r\n",
															cgu.CommonUtil.GetColumnPropertyName(objColumn));
				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("<ItemTemplate>\r\n");
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("<asp:CheckBox Enabled='false' ID='{0}Item' runat='server' Checked='<%# Eval(\"{0}\") %>' />\r\n", cgu.CommonUtil.GetColumnPropertyName(objColumn));
				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("</ItemTemplate>\r\n");

				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("<EditItemTemplate>\r\n");
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("<asp:CheckBox ID='{0}Edit' runat='server' Checked='<%# Bind(\"{0}\") %>' />\r\n", cgu.CommonUtil.GetColumnPropertyName(objColumn));
				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("</EditItemTemplate>\r\n");

				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("<InsertItemTemplate>\r\n");
				sBuilder.Append('\t', iIndentLevel + 2);
				sBuilder.AppendFormat("<asp:CheckBox ID='{0}Insert' runat='server' Checked='<%# Bind(\"{0}\") %>' />\r\n", cgu.CommonUtil.GetColumnPropertyName(objColumn));
				sBuilder.Append('\t', iIndentLevel + 1);
				sBuilder.Append("</InsertItemTemplate>\r\n");

				sBuilder.Append('\t', iIndentLevel);
				sBuilder.AppendFormat("</asp:TemplateField>\r\n");

				return sBuilder.ToString();
			}
			
			//------------------------------------------------------------------------
			//otherwise, bind a textbox
			//------------------------------------------------------------------------
			//some types require special attributes
			string sSqlDataType = objColumn.SQLDatatype;
			switch (sSqlDataType)
			{
				case "char":
				case "varchar":
				case "nchar":
				case "nvarchar":
					sBuilderAdd.AppendFormat(" ConvertEmptyStringToNull=\"{0}\"", objColumn.IsNullable.ToString()); 
					break;

				case "byte":
				case "binary":
				case "varbinary":
				case "smallint":
				case "int":
				case "bigint":
				case "money":
				case "smallmoney":
					break;

				case "datetime":
				case "smalldatetime":
					sBuilderAdd.Append(" DataFormatString=\"{0:d}\" HtmlEncode=\"False\"");
					break;

				case "decimal":
				case "numeric":
				case "float":
				case "real":
				case "tinyint":
				case "bit":
				case "cursor":
				case "ntext":
				case "image":
				case "sql_variant":
				case "table":
				case "timestamp":
				case "uniqueidentifier":
				case "text":
				case "xml":
					break;

				default:
					throw new Exception(sSqlDataType + " is not a recognized datatype.");
			}

			sBuilder.AppendFormat("<asp:BoundField ControlStyle-CssClass=\"amGridControlStyle\" ReadOnly=\"{0}\" HeaderText=\"{1}\" DataField=\"{1}\" InsertVisible=\"{3}\" SortExpression=\"{1}\" {2}/>",
											bReadOnly.ToString(), cgu.CommonUtil.GetColumnPropertyName(objColumn), sBuilderAdd, bInsertVisible.ToString());

			//return the line
			return sBuilder.ToString();
		}

		/// <summary>
		/// Builds a templated detailsview column definition with a drop-down displaying text from the referenced foreign key table.
		/// </summary>
		internal static string GetUiGridColumnFkDdTemplate(enmControlViewType enmControl, Table objTable, Column objColumn, int iIndentLevel, bool bReadOnly, bool bInsertVisible)
		{
			StringBuilder sBuilder = new StringBuilder("\r\n");
			Key fKeyReference = null;
			
			//fetch the Text column name which corresponds foreign key value 
			string sTextColName = "";
			string sDataColName = "";
			foreach (Key fKey in objTable.Keys)
			{
				//see if the first key column in this foreign key matches the column name passed in. 
				if (fKey.KeyColumns[0].Name == objColumn.Name)
				{
					fKeyReference = fKey;

					//fetch first string-based field in the reference table and assume it provides a description of the value (todo: better approach?)
					if (fKey.ReferencedTable != null)
					{
						sDataColName = cgu.CommonUtil.GetColumnPropertyName(fKey.ReferencedKey.KeyColumns[0].Name);

						foreach (Column col in fKey.ReferencedTable.Columns)
						{
							if (cgu.CommonUtil.IsColumnStringType(col))
							{
								sTextColName = cgu.CommonUtil.GetColumnPropertyName(col.Name);
								break;
							}
						}
					}
				}
			}

			//if no columns found (or more than 1) return an empty string
			if ((sTextColName.Length == 0) || (fKeyReference == null))
			{
				return String.Empty;
			}

			//if the column is a foreign key, return a templated def with a drop-down showing the text value of the referenced fk
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat("<asp:TemplateField ControlStyle-CssClass=\"amGridControlStyle\" HeaderText=\"{0}\">\r\n",
			                      cgu.CommonUtil.GetColumnPropertyName(objColumn));
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<ItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.AppendFormat(
														"<asp:DropDownList Id='dd{0}Item' Runat='server' CssClass='asDropDownStyle' DataSourceID='dataDropDownFk{0}' DataTextField='{1}' DataValueField='{2}' ReadOnly='True' >\r\n",
														cgu.CommonUtil.GetColumnPropertyName(objColumn),
														sTextColName,
														sDataColName);
			sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.Append("</asp:DropDownList>\r\n");
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</ItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<EditItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.AppendFormat(
														"<asp:DropDownList Id='dd{0}Edit' Runat='server' CssClass='asDropDownStyle' DataSourceID='dataDropDownFk{0}' DataTextField='{1}' DataValueField='{2}' SelectedValue='<%# Bind(\"{3}\")%>'>\r\n",
														cgu.CommonUtil.GetColumnPropertyName(objColumn),
														sTextColName,
														sDataColName,
														cgu.CommonUtil.GetColumnPropertyName(objColumn));
			sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.Append("</asp:DropDownList>\r\n");
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</EditItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("<InsertItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.AppendFormat(
														"<asp:DropDownList Id='dd{0}Insert' Runat='server' CssClass='asDropDownStyle' DataSourceID='dataDropDownFk{0}' DataTextField='{1}' DataValueField='{2}' SelectedValue='<%# Bind(\"{3}\")%>'>\r\n",
														cgu.CommonUtil.GetColumnPropertyName(objColumn),
														sTextColName,
														sDataColName,
														cgu.CommonUtil.GetColumnPropertyName(objColumn));
													sBuilder.Append('\t', iIndentLevel + 2);
			sBuilder.Append("</asp:DropDownList>\r\n");
			sBuilder.Append('\t', iIndentLevel + 1);
			sBuilder.Append("</InsertItemTemplate>\r\n");
			sBuilder.Append('\t', iIndentLevel);
			sBuilder.AppendFormat("</asp:TemplateField>\r\n");

			//return the line
			return sBuilder.ToString();
		}

		// ************************************************************************************
		#endregion table ui-related functions
		// ************************************************************************************
	}
}